<?php

namespace app\controller;

use app\BaseController;
use Lp\GameCenter\Shared\Domain\DateTime;
use think\Request;
use think\Response;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\IOFactory;
use app\validate\CategoryValidate;
use app\model\GameCategoryModel;
use think\facade\Filesystem;
use think\facade\Log;


class ProductTags extends BaseController {
    // 新增分类
    public function create()
    {
        // 1. 自动解析 JSON 参数（TP8.1 内置支持）
        $data = request()->post();

        // 2. 基础参数校验（防御性编程）
        if (empty($data) || !is_array($data)) {
            Log::notice('EMPTY_REQUEST_DATA', ['input' => request()->getContent()]);
            return json(['code' => 422, 'msg' => '请提交有效的 JSON 数据'], 422);
        }

        // 3. 校验必填字段（防止字段缺失）
        $requiredFields = ['name', 'order'];
        foreach ($requiredFields as $field) {
            if (!isset($data[$field]) || trim($data[$field]) === '') {
                Log::notice('MISSING_REQUIRED_FIELD', ['field' => $field, 'data' => $data]);
                return json([
                    'code' => 422,
                    'msg' => "字段「{$field}」是必填项"
                ], 422);
            }
        }

        // 4. 类型强制转换（防止参数污染）
        $data = [
            'name'  => trim($data['name']), // 去除空格
            'order' => (int) $data['order'] // 确保为整数
        ];

        try {
            // ❶ 强制使用原生 SQL 插入（VALUES 格式）
            $result = Db::name('game_category')->insertGetId([
                'name'       => $data['name'],
                'order'      => $data['order'],
                'created_at' => date('Y-m-d H:i:s'), // 显式赋值时间戳
                'updated_at' => date('Y-m-d H:i:s')
            ]);

            // ❷ 验证自增 ID（必须 > 0）
            if ($result <= 0) {
                Log::error('VALUES_INSERT_FAILED', [
                    'sql' => Db::getLastSql(), // 确认 SQL 为 VALUES 格式
                    'error' => Db::getError()
                ]);
                return json(['code' => 500, 'msg' => '自增 ID 生成失败，请检查数据库'], 500);
            }

            // ❸ 直接查询数据库（验证数据存在）
            $verify = Db::name('game_category')
                ->where('id', $result)
                ->find();

            if (empty($verify)) {
                Log::critical('VALUES_DATA_LOST', [
                    'insert_id' => $result,
                    'select_sql' => Db::getLastSql()
                ]);
                return json(['code' => 500, 'msg' => '数据写入后丢失，检查事务设置'], 500);
            }

            return json([
                'code' => 0,
                'msg' => '创建成功',
                'data' => [
                    'id'         => (string) $verify['id'], // 字符串 ID 防丢失
                    'name'       => $verify['name'],
                    'order'      => $verify['order'],
                    'created_at' => $verify['created_at'],
                    'updated_at' => $verify['updated_at']
                ]
            ]);

        } catch (\Exception $e) {
            Log::critical('VALUES_EXCEPTION', [
                'message' => $e->getMessage(),
                'sql' => Db::getLastSql()
            ]);
            return json(['code' => 500, 'msg' => '系统错误，请联系管理员'], 500);
        }
    }



    // 分类列表（分页+搜索）
    public function list()
    {
        $page = $this->request->get('page', 1);
        $pageSize = $this->request->get('page_size', 10);
        $searchId = $this->request->get('id');
        $searchName = $this->request->get('name');

        $query = GameCategoryModel::field('id,name,order,updated_at');
        if ($searchId) $query->where('id', $searchId);
        if ($searchName) $query->whereLike('name', "%{$searchName}%");

        $list = $query->order('order asc, id desc')
            ->paginate(['page' => $page, 'list_rows' => $pageSize]);

        return json([
            'code' => 0,
            'msg' => 'success',
            'data' => [
                'list' => $list->items(),
                'total' => $list->total()
            ]
        ]);
    }

    // 分类详情
    public function detail($id)
    {
        $data = GameCategoryModel::find($id);
        return $data ?
            json(['code' => 0, 'msg' => 'success', 'data' => $data]) :
            json(['code' => 1, 'msg' => '数据不存在']);
    }

    // 编辑分类
    public function update($id)
    {
        $data =  $this->request->post();
        try {
            $result = Db::name('game_category')->where('id', $id)->update($data);
            if ($result) {
                return json(['code' => 0, 'msg' => '编辑成功', 'data' => []]);
            } else {
                return json(['code' => 1, 'msg' => '未找到对应记录', 'data' => []]);
            }
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '编辑失败: ' . $e->getMessage(), 'data' => []]);
        }
    }

    // 删除分类
    public function delete($id)
    {
        GameCategoryModel::destroy($id);
        return json(['code' => 0, 'msg' => '删除成功']);
    }


    // 数据导入
    public function import(Request $request)
    {
        $file = $request->file('file');
        if (!$file) return json(['code' => 1, 'msg' => '请选择文件']);

        $ext = $file->getOriginalExtension();
        if (!in_array($ext, ['xlsx', 'txt'])) {
            return json(['code' => 1, 'msg' => '仅支持xlsx/txt文件']);
        }

        $tempPath = Filesystem::putFileAs('temp', $file, uniqid().'.'.$ext);
        $fullPath = Filesystem::path($tempPath);

        try {
            // 处理Excel
            if ($ext === 'xlsx') {
                $spreadsheet = IOFactory::load($fullPath);
                $sheet = $spreadsheet->getActiveSheet();
                $rows = $sheet->toArray();
            }
            // 处理TXT
            else {
                $rows = array_map('str_getcsv', file($fullPath));
            }

            $success = 0;
            foreach ($rows as $row) {
                // 跳过标题行 & 空行
                if (!isset($isHeaderSkipped)) {
                    $isHeaderSkipped = true;
                    continue;
                }
                if (empty(array_filter($row))) continue;

                try {
                    GameCategoryModel::create([
                        'name'  => $row[0],
                        'order' => $row[1]
                    ]);
                    $success++;
                } catch (\Exception $e) {
                    continue; // 忽略重复项
                }
            }

            return json([
                'code' => 0,
                'msg' => "导入成功{$success}条",
                'data' => ['success' => $success]
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '文件处理失败：'.$e->getMessage()]);
        } finally {
            @unlink($fullPath); // 清理临时文件
        }
    }



    // 数据导出
    public function export(Request $request)
    {
        $searchId = $request->param('id');
        $searchName = $request->param('name');

        // 构建查询
        $query = GameCategoryModel::field('id,name,order,updated_at');
        if ($searchId) $query->where('id', $searchId);
        if ($searchName) $query->whereLike('name', "%{$searchName}%");
        $data = $query->order('order asc')->select();

        // 准备导出数据
        $exportData = [
            ['ID', '分类名称', '排序值', '最后更新时间'] // 表头
        ];
        foreach ($data as $item) {
            $exportData[] = [
                $item->id,
                $item->name,
                $item->order,
                $item->updated_at
            ];
        }

        // 生成文件名
        $filename = date('Ymd').'_页签分类列表.xlsx';

        // 输出Excel
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 写入数据
        $sheet->fromArray($exportData, null, 'A1');

        // 设置样式
        $sheet->getStyle('A1:D1')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                'color' => ['argb' => 'FFD9D9D9']
            ]
        ]);

        // 自动调整列宽
        foreach (range('A', $sheet->getHighestColumn()) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // 输出到浏览器
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.rawurlencode($filename).'"');
        header('Cache-Control: max-age=0');
        header('Pragma: public');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}